/* Formatted on 2014/05/02 19:07 (Formatter Plus v4.8.0) */


CREATE OR REPLACE PACKAGE BODY wbscus_qp_pricing_engine_pkg
IS
--|------------|-----|---------------------|--------------------------------------------------------
--|Date        |Ver #|Name                 |Remarks
--|------------|-----|---------------------|--------------------------------------------------------
--|21-Jan-2014 |1.0  |NDantu               |New Version
--|11-Feb-2014 |1.1  |NDantu               |Integrating with APEX.
--|02-May-2014 |1.2  |NDantu               |Renamed Column CART_LINE_ID to CART_LINE_NO.
--|------------|-----|---------------------|--------------------------------------------------------
   g_request_type_code   VARCHAR2 (20)  := 'ONT';
   g_debug               VARCHAR2 (1)   := 'Y';
   g_dir_path            VARCHAR2 (100);
   g_directory_name      VARCHAR2 (200) := 'XX_OM_DEBUG_DIR';
   g_module              VARCHAR2 (200) := 'WBSCUS_QP_ENGINE';
   g_order_source_id     NUMBER         := 0;
   g_legacy_system_id    NUMBER         := 1;

   /* Procedure to write Debug into FND_LOG_MESSAGES */
   PROCEDURE DEBUG (p_message VARCHAR)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      IF g_debug = 'Y'
      THEN
         DBMS_OUTPUT.put_line (SUBSTRB (p_message, 1, 255));

         INSERT INTO fnd_log_messages
                     (module, log_level,
                      MESSAGE_TEXT,
                      user_id, TIMESTAMP, log_sequence
                     )
              VALUES (g_module, 1,
                      SUBSTRB (NVL (p_message, 'NULL MESSAAGE'), 1, 2000),
                      1, SYSDATE, fnd_log_messages_s.NEXTVAL
                     );
      END IF;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END DEBUG;

   /* Procedure Initialize; Enable OE Debug; needs physcial path to write the Debug*/
   PROCEDURE initialize
   IS
      CURSOR cr_dir_path
      IS
         SELECT directory_path
           FROM all_directories
          WHERE directory_name = g_directory_name;
   BEGIN
      OPEN cr_dir_path;

      FETCH cr_dir_path
       INTO g_dir_path;

      CLOSE cr_dir_path;

      IF g_debug = 'Y'
      THEN
         oe_debug_pub.setdebuglevel (10);
         oe_debug_pub.g_dir := g_dir_path;
         oe_debug_pub.initialize;
         oe_debug_pub.debug_on;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in Initialize Procedure ' || SQLERRM);
   END initialize;

   /* Procedure Set Order Header*/
   PROCEDURE set_header (p_order_header_rec wbscus_order_header_obj)
   IS
      CURSOR cr_currency
      IS
         SELECT base_currency_code
           FROM ap_system_parameters_all
          WHERE org_id = p_order_header_rec.org_id;
   BEGIN
      -- Initialize with G_MISS_HEADER_REC;
      oe_order_pub.g_hdr := oe_order_pub.g_miss_header_rec;
      oe_order_pub.g_hdr.org_id := p_order_header_rec.org_id;
      oe_order_pub.g_hdr.sold_to_org_id :=
                                       p_order_header_rec.sold_to_customer_id;
      oe_order_pub.g_hdr.price_list_id := p_order_header_rec.price_list_id;
      oe_order_pub.g_hdr.order_type_id := p_order_header_rec.order_type_id;
      oe_order_pub.g_hdr.order_source_id := g_order_source_id;
      oe_order_pub.g_hdr.sales_channel_code :=
                                        p_order_header_rec.sales_channel_code;

      OPEN cr_currency;

      FETCH cr_currency
       INTO oe_order_pub.g_hdr.transactional_curr_code;

      CLOSE cr_currency;

      oe_order_pub.g_hdr.ordered_date := p_order_header_rec.order_date;
      oe_order_pub.g_hdr.ship_to_org_id := p_order_header_rec.ship_to_org_id;
      oe_order_pub.g_hdr.invoice_to_org_id :=
                                         p_order_header_rec.invoice_to_org_id;
      oe_order_pub.g_hdr.shipment_priority_code :=
                                    p_order_header_rec.shipment_priority_code;
      DEBUG ('Set Header Completed');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in set_header' || SQLERRM);
   END set_header;

   /* Procedure Set Order Line*/
   PROCEDURE set_line (p_order_line_rec wbscus_order_line_rec)
   IS
   BEGIN
      -- Initialize with G_MISS_LINE_REC;
      oe_order_pub.g_line := oe_order_pub.g_miss_line_rec;
      oe_order_pub.g_line.line_number := p_order_line_rec.line_number;
      oe_order_pub.g_line.inventory_item_id :=
                                           p_order_line_rec.inventory_item_id;
      oe_order_pub.g_line.ship_from_org_id :=
                                            p_order_line_rec.ship_from_org_id;
      oe_order_pub.g_line.ordered_quantity :=
                                            p_order_line_rec.ordered_quantity;
      oe_order_pub.g_line.pricing_date := SYSDATE;
      oe_order_pub.g_line.schedule_ship_date :=
                                          p_order_line_rec.schedule_ship_date;
      oe_order_pub.g_line.shipment_priority_code :=
                                      p_order_line_rec.shipment_priority_code;
      oe_order_pub.g_line.payment_term_id := p_order_line_rec.payment_term_id;
      oe_order_pub.g_line.price_list_id :=
         NVL (p_order_line_rec.price_list_id,
              oe_order_pub.g_hdr.price_list_id
             );
      oe_order_pub.g_line.org_id := oe_order_pub.g_hdr.org_id;
      oe_order_pub.g_line.calculate_price_flag := 'Y';
      oe_order_pub.g_line.ship_to_org_id := p_order_line_rec.ship_to_org_id;
      oe_order_pub.g_line.invoice_to_org_id :=
                                            p_order_line_rec.invoice_to_org_id;
      -- Below are Harcoded, Needs to get the Inputs.
      oe_order_pub.g_line.flow_status_code := 'ENTERED';
      oe_order_pub.g_line.line_category_code := 'ORDER';
      oe_order_pub.g_line.order_quantity_uom :=
                                             p_order_line_rec.primary_uom_code;
      DEBUG ('Set Line Completed');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in set_Line ' || SQLERRM);
   END set_line;

   PROCEDURE copy_line_to_table (
      p_line_index            NUMBER,
      p_line_tbl     IN OUT   qp_preq_grp.line_tbl_type
   )
   IS
      line_rec   qp_preq_grp.line_rec_type;
   BEGIN
      line_rec.request_type_code := g_request_type_code;
      line_rec.line_index := p_line_index;
      line_rec.line_type_code := 'LINE';
      line_rec.pricing_effective_date := SYSDATE;
      line_rec.active_date_first := SYSDATE;
      line_rec.active_date_second := SYSDATE;
      line_rec.active_date_first_type := 'NO TYPE';
      line_rec.active_date_second_type := 'NO TYPE';
      line_rec.line_quantity := oe_order_pub.g_line.ordered_quantity;
      line_rec.line_uom_code := oe_order_pub.g_line.order_quantity_uom;
      line_rec.price_flag := oe_order_pub.g_line.calculate_price_flag;
      line_rec.line_id := oe_order_pub.g_line.line_id;
      line_rec.currency_code := oe_order_pub.g_hdr.transactional_curr_code;
      p_line_tbl (p_line_index) := line_rec;
      DEBUG ('copy_line_to_table Completed');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in copy_line_to_table ' || SQLERRM);
   END copy_line_to_table;

   -- Copy the pricing engine Attributes to Request Output
   PROCEDURE copy_attributes_to_request (
      p_line_index                               NUMBER,
      p_pricing_contexts_tbl                     qp_attr_mapping_pub.contexts_result_tbl_type,
      p_qualifier_contexts_tbl                   qp_attr_mapping_pub.contexts_result_tbl_type,
      px_req_line_attr_tbl       IN OUT NOCOPY   qp_preq_grp.line_attr_tbl_type,
      px_req_qual_tbl            IN OUT NOCOPY   qp_preq_grp.qual_tbl_type
   )
   IS
      i              PLS_INTEGER := 0;
      l_attr_index   PLS_INTEGER := NVL (px_req_line_attr_tbl.LAST, 0);
      l_qual_index   PLS_INTEGER := NVL (px_req_qual_tbl.LAST, 0);
   BEGIN
      i := p_pricing_contexts_tbl.FIRST;

      WHILE i IS NOT NULL
      LOOP
         l_attr_index := l_attr_index + 1;
         px_req_line_attr_tbl (l_attr_index).validated_flag := 'Y';
         px_req_line_attr_tbl (l_attr_index).line_index := p_line_index;
         px_req_line_attr_tbl (l_attr_index).pricing_context :=
                                      p_pricing_contexts_tbl (i).context_name;
         px_req_line_attr_tbl (l_attr_index).pricing_attribute :=
                                    p_pricing_contexts_tbl (i).attribute_name;
         px_req_line_attr_tbl (l_attr_index).pricing_attr_value_from :=
                                   p_pricing_contexts_tbl (i).attribute_value;
         i := p_pricing_contexts_tbl.NEXT (i);
      END LOOP;

      i := p_qualifier_contexts_tbl.FIRST;

      WHILE i IS NOT NULL
      LOOP
         l_qual_index := l_qual_index + 1;

         IF     p_qualifier_contexts_tbl (i).context_name = 'MODLIST'
            AND p_qualifier_contexts_tbl (i).attribute_name =
                                                        'QUALIFIER_ATTRIBUTE4'
         THEN
            IF     oe_order_pub.g_line.agreement_id IS NOT NULL
               AND oe_order_pub.g_line.agreement_id <> fnd_api.g_miss_num
            THEN
               px_req_qual_tbl (l_qual_index).validated_flag := 'Y';
            ELSE
               px_req_qual_tbl (l_qual_index).validated_flag := 'N';
            END IF;
         ELSE
            px_req_qual_tbl (l_qual_index).validated_flag := 'N';
         END IF;

         px_req_qual_tbl (l_qual_index).line_index := p_line_index;
         px_req_qual_tbl (l_qual_index).qualifier_context :=
                                     p_qualifier_contexts_tbl (i).context_name;
         px_req_qual_tbl (l_qual_index).qualifier_attribute :=
                                   p_qualifier_contexts_tbl (i).attribute_name;
         px_req_qual_tbl (l_qual_index).qualifier_attr_value_from :=
                                  p_qualifier_contexts_tbl (i).attribute_value;
         i := p_qualifier_contexts_tbl.NEXT (i);
      END LOOP;

      DEBUG ('copy_attributes_to_request Completed');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('copy_attributes_to_request ' || SQLERRM);
   END copy_attributes_to_request;

   -- Capture the Price Engine API Results; This is for Debug purpose. Price Results.
   PROCEDURE display_line_table (p_line_tbl qp_preq_grp.line_tbl_type)
   IS
      i   NUMBER;
   BEGIN
      i := p_line_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG
               ('--------------------< Line >---------------------------------'
               );
            DEBUG ('Line Index: ' || p_line_tbl (i).line_index);
            DEBUG ('Unit_price: ' || p_line_tbl (i).unit_price);
            DEBUG ('Percent price: ' || p_line_tbl (i).percent_price);
            DEBUG (   'Adjusted Unit Price: '
                   || p_line_tbl (i).adjusted_unit_price
                  );
            DEBUG ('Pricing status code: ' || p_line_tbl (i).status_code);
            DEBUG ('Pricing status text: ' || p_line_tbl (i).status_text);
            EXIT WHEN i = p_line_tbl.LAST;
            i := p_line_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG
         ('-----------------------------------< End of Line >----------------------------'
         );
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('display_line_table ' || SQLERRM);
   END display_line_table;

   -- Capture the Price Engine API Results; This is for Debug purpose.Selected Qualifiers
   PROCEDURE display_line_table_qualifier (
      p_line_qual   qp_preq_grp.qual_tbl_type
   )
   IS
      i   NUMBER;
   BEGIN
      i := p_line_qual.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG ('Line Index: ' || p_line_qual (i).line_index);
            DEBUG ('qualifier_context: ' || p_line_qual (i).qualifier_context);
            DEBUG (   'qualifier_attribute: '
                   || p_line_qual (i).qualifier_attribute
                  );
            DEBUG (   'qualifier_attr_value_from: '
                   || p_line_qual (i).qualifier_attr_value_from
                  );
            DEBUG (   'qualifier_attr_value_to: '
                   || p_line_qual (i).qualifier_attr_value_to
                  );
            DEBUG (   'comparison_operator_code: '
                   || p_line_qual (i).comparison_operator_code
                  );
            DEBUG ('validated_flag: ' || p_line_qual (i).validated_flag);
            DEBUG ('status_code: ' || p_line_qual (i).status_code);
            DEBUG ('status_text: ' || p_line_qual (i).status_text);
            EXIT WHEN i = p_line_qual.LAST;
            i := p_line_qual.NEXT (i);
         END LOOP;
      END IF;

      DEBUG
         ('-----------------------------------< End of Line Qualifier >----------------------------'
         );
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('display_line_table_qualifier ' || SQLERRM);
   END display_line_table_qualifier;

   -- Capture the Price Engine API Results; This is for Debug purpose.Pricng Attributes.
   PROCEDURE display_line_table_attributes (
      p_line_attr_tbl   qp_preq_grp.line_attr_tbl_type
   )
   IS
      i   NUMBER;
   BEGIN
      DEBUG
         ('-----------------------------------< Line Attribute >----------------------------'
         );
      i := p_line_attr_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG ('Line Index: ' || p_line_attr_tbl (i).line_index);
            DEBUG ('pricing_context: ' || p_line_attr_tbl (i).pricing_context);
            DEBUG (   'pricing_attribute: '
                   || p_line_attr_tbl (i).pricing_attribute
                  );
            DEBUG (   'pricing_attr_value_from: '
                   || p_line_attr_tbl (i).pricing_attr_value_from
                  );
            DEBUG (   'pricing_attr_value_to: '
                   || p_line_attr_tbl (i).pricing_attr_value_to
                  );
            DEBUG ('validated_flag: ' || p_line_attr_tbl (i).validated_flag);
            DEBUG ('status_code: ' || p_line_attr_tbl (i).status_code);
            DEBUG ('status_text: ' || p_line_attr_tbl (i).status_text);
            EXIT WHEN i = p_line_attr_tbl.LAST;
            i := p_line_attr_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG
         ('-----------------------------------< End of Line Attribute >----------------------------'
         );
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('display_line_table_attributes ' || SQLERRM);
   END display_line_table_attributes;

   -- Capture the Price Engine API Results; This is for Debug purpose.Eligible Discounts
   PROCEDURE display_line_dtl_tabl (
      p_line_detail_tbl   qp_preq_grp.line_detail_tbl_type
   )
   IS
      i   NUMBER;
   BEGIN
      DEBUG
         ('-----------------------------------< Line Detail >----------------------------'
         );
      DEBUG ('------------Price List/Discount Information------------');
      i := p_line_detail_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG ('Index: ' || i);
            DEBUG ('Line Index: ' || p_line_detail_tbl (i).line_index);
            DEBUG (   'Line Detail Index: '
                   || p_line_detail_tbl (i).line_detail_index
                  );
            DEBUG (   'Line Detail Type:'
                   || p_line_detail_tbl (i).line_detail_type_code
                  );
            DEBUG ('List Header Id: ' || p_line_detail_tbl (i).list_header_id);
            DEBUG ('List Line Id: ' || p_line_detail_tbl (i).list_line_id);
            DEBUG (   'List Line Type Code: '
                   || p_line_detail_tbl (i).list_line_type_code
                  );
            DEBUG (   'Adjustment Amount : '
                   || p_line_detail_tbl (i).adjustment_amount
                  );
            DEBUG ('Line Quantity : ' || p_line_detail_tbl (i).line_quantity);
            DEBUG (   'Operand Calculation Code: '
                   || p_line_detail_tbl (i).operand_calculation_code
                  );
            DEBUG ('Operand value: ' || p_line_detail_tbl (i).operand_value);
            DEBUG ('Automatic Flag: ' || p_line_detail_tbl (i).automatic_flag);
            DEBUG ('Override Flag: ' || p_line_detail_tbl (i).override_flag);
            DEBUG ('status_code: ' || p_line_detail_tbl (i).status_code);
            DEBUG ('status text: ' || p_line_detail_tbl (i).status_text);
            DEBUG (   'inventory_item_id: '
                   || p_line_detail_tbl (i).inventory_item_id
                  );
            DEBUG ('-------------------------------------------');
            EXIT WHEN i = p_line_detail_tbl.LAST;
            i := p_line_detail_tbl.NEXT (i);
         END LOOP;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('display_line_dtl_tabl ' || SQLERRM);
   END display_line_dtl_tabl;

   -- Capture the Price Engine API Results; This is for Debug purpose.Line Detail Qualifiers
   PROCEDURE display_line_dtl_qual (
      p_line_detail_qual_tbl   qp_preq_grp.line_detail_qual_tbl_type
   )
   IS
      i   NUMBER;
   BEGIN
      DEBUG
         ('-----------------------------------< Line Detail Qualifier >----------------------------'
         );
      DEBUG ('-----------Qualifier Attributes Information-------------');
      i := p_line_detail_qual_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG (   'Line Detail Index '
                   || p_line_detail_qual_tbl (i).line_detail_index
                  );
            DEBUG ('Context ' || p_line_detail_qual_tbl (i).qualifier_context);
            DEBUG (   'Attribute '
                   || p_line_detail_qual_tbl (i).qualifier_attribute
                  );
            DEBUG (   'Value '
                   || p_line_detail_qual_tbl (i).qualifier_attr_value_from
                  );
            DEBUG ('Status Code ' || p_line_detail_qual_tbl (i).status_code);
            DEBUG ('---------------------------------------------------');
            EXIT WHEN i = p_line_detail_qual_tbl.LAST;
            i := p_line_detail_qual_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG
         ('-----------------------------------< End of Line Detail Qualifier >----------------------------'
         );
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('display_line_dtl_qual ' || SQLERRM);
   END display_line_dtl_qual;

   -- Capture the Price Engine API Results; This is for Debug purpose.Line Detail Pricing Attributes
   PROCEDURE display_line_dtl_attributes (
      p_line_detail_attr_tbl   qp_preq_grp.line_detail_attr_tbl_type
   )
   IS
      i   NUMBER;
   BEGIN
      DEBUG
         ('-----------------------------------< Line Detail Attributes >----------------------------'
         );
      DEBUG
         ('-----------------------------------< Pricing Attributes Information >----------------------------'
         );
      i := p_line_detail_attr_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG ('line_index ' || p_line_detail_attr_tbl (i).line_index);
            DEBUG (   'Line detail Index '
                   || p_line_detail_attr_tbl (i).line_detail_index
                  );
            DEBUG ('Context ' || p_line_detail_attr_tbl (i).pricing_context);
            DEBUG ('Attribute '
                   || p_line_detail_attr_tbl (i).pricing_attribute
                  );
            DEBUG (   'Value '
                   || p_line_detail_attr_tbl (i).pricing_attr_value_from
                  );
            DEBUG ('Status Code ' || p_line_detail_attr_tbl (i).status_code);
            DEBUG ('---------------------------------------------------');
            EXIT WHEN i = p_line_detail_attr_tbl.LAST;
            i := p_line_detail_attr_tbl.NEXT (i);
         END LOOP;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('display_line_dtl_attributes ' || SQLERRM);
   END display_line_dtl_attributes;

   -- Capture the Price Engine API Results; This is for Debug purpose.Other Item Discounts; GAP; ADJ_RELATIONSHIPS verifications
   PROCEDURE display_other_line_items (
      p_related_lines_tbl   qp_preq_grp.related_lines_tbl_type
   )
   IS
      i   NUMBER;
   BEGIN
      DEBUG
         ('--------------< Related Lines Information for Price Breaks/Service Items >---------------'
         );
      i := p_related_lines_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG ('Line Index :' || p_related_lines_tbl (i).line_index);
            DEBUG (   'Line Detail Index: '
                   || p_related_lines_tbl (i).line_detail_index
                  );
            DEBUG (   'Relationship Type Code: '
                   || p_related_lines_tbl (i).relationship_type_code
                  );
            DEBUG (   'Related Line Index: '
                   || p_related_lines_tbl (i).related_line_index
                  );
            DEBUG (   'Related Line Detail Index: '
                   || p_related_lines_tbl (i).related_line_detail_index
                  );
            DEBUG ('Status Code: ' || p_related_lines_tbl (i).status_code);
            EXIT WHEN i = p_related_lines_tbl.LAST;
            i := p_related_lines_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG
         ('--------------< End of Related Lines Information for Price Breaks/Service Items >---------------'
         );
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('display_other_line_items' || SQLERRM);
   END display_other_line_items;

   -- Derive the Inventory Item ID;
   PROCEDURE get_item_id (
      p_line_index                      NUMBER,
      p_line_detail_tbl        IN       qp_preq_grp.line_detail_tbl_type,
      p_line_detail_attr_tbl   IN       qp_preq_grp.line_detail_attr_tbl_type,
      p_inventory_item_id      OUT      NUMBER,
      p_segment1               OUT      VARCHAR2
   )
   IS
      CURSOR cr_item
      IS
         SELECT segment1
           FROM mtl_system_items
          WHERE inventory_item_id = p_inventory_item_id;

      l_line_detail_index   NUMBER;
      i                     NUMBER;
   BEGIN
      l_line_detail_index := 0;
      p_inventory_item_id := -11;
      i := p_line_detail_tbl.FIRST;
      DEBUG ('**** Checking the Item for Line ' || p_line_index);

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG (   'Processing p_line_detail_tbl (i).line_index as '
                   || p_line_detail_tbl (i).line_index
                   || '-'
                   || p_line_detail_tbl (i).list_line_type_code
                  );

            IF     p_line_detail_tbl (i).line_index = p_line_index
               AND p_line_detail_tbl (i).list_line_type_code = 'PLL'
            THEN
               l_line_detail_index := p_line_detail_tbl (i).line_detail_index;
               DEBUG ('Found the Line Detail Index as ' || l_line_detail_index
                     );
               EXIT;
            END IF;

            EXIT WHEN i = p_line_detail_tbl.LAST;
            i := p_line_detail_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG ('Identified the l_line_detail_index  as ' || l_line_detail_index);
      i := p_line_detail_attr_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG
               (   'Processing p_line_detail_attr_tbl (i).pricing_context   as '
                || p_line_detail_attr_tbl (i).pricing_context
                || '-'
                || p_line_detail_attr_tbl (i).pricing_attribute
               );

            IF     p_line_detail_attr_tbl (i).pricing_context = 'ITEM'
               AND p_line_detail_attr_tbl (i).pricing_attribute =
                                                          'PRICING_ATTRIBUTE1'
               AND l_line_detail_index =
                                  p_line_detail_attr_tbl (i).line_detail_index
            THEN
               p_inventory_item_id :=
                           p_line_detail_attr_tbl (i).pricing_attr_value_from;

               OPEN cr_item;

               FETCH cr_item
                INTO p_segment1;

               CLOSE cr_item;

               DEBUG (   'Found the Item as '
                      || p_inventory_item_id
                      || '='
                      || p_segment1
                     );
               EXIT;
            END IF;

            EXIT WHEN i = p_line_detail_attr_tbl.LAST;
            i := p_line_detail_attr_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG ('get_item_id Completed ');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('get_item_id ' || SQLERRM);
   END get_item_id;

   PROCEDURE get_input_line_rec (
      p_order_lines_tbl   IN       wbscus_order_lines_obj,
      p_line_number       IN       NUMBER,
      x_order_lines_rec   OUT      wbscus_order_line_rec
   )
   IS
      i   NUMBER;
      j   NUMBER;
   BEGIN
      i := p_order_lines_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            IF p_line_number = p_order_lines_tbl (i).line_number
            THEN
               x_order_lines_rec := p_order_lines_tbl (i);
               EXIT;
            END IF;

            EXIT WHEN i = p_order_lines_tbl.LAST;
            i := p_order_lines_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG ('get_input_line_rec Completed ');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('get_input_line_rec ' || SQLERRM);
   END get_input_line_rec;

   PROCEDURE assign_lines (
      p_line_tbl                     qp_preq_grp.line_tbl_type,
      p_line_detail_tbl              qp_preq_grp.line_detail_tbl_type,
      p_line_detail_attr_tbl         qp_preq_grp.line_detail_attr_tbl_type,
      p_order_lines_tbl              wbscus_order_lines_obj,
      x_order_lines_tbl        OUT   wbscus_order_lines_obj
   )
   IS
      i            NUMBER;
      l_counter    NUMBER                := 1;
      l_line_rec   wbscus_order_line_rec;
   BEGIN
      x_order_lines_tbl := wbscus_order_lines_obj ();
      i := p_line_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            IF TRIM (p_line_tbl (i).line_type_code) = 'LINE'
            THEN
               get_input_line_rec (p_order_lines_tbl,
                                   p_line_tbl (i).line_index,
                                   l_line_rec
                                  );
               x_order_lines_tbl.EXTEND;
               x_order_lines_tbl (l_counter) := l_line_rec;
               x_order_lines_tbl (l_counter).line_number :=
                                                     p_line_tbl (i).line_index;
               x_order_lines_tbl (l_counter).ordered_quantity :=
                                                  p_line_tbl (i).line_quantity;
               x_order_lines_tbl (l_counter).unit_list_price :=
                                                     p_line_tbl (i).unit_price;
               x_order_lines_tbl (l_counter).unit_selling_price :=
                                            p_line_tbl (i).adjusted_unit_price;
               x_order_lines_tbl (l_counter).tax_amount := 0;
               x_order_lines_tbl (l_counter).primary_uom_code :=
                                                  p_line_tbl (i).line_uom_code;
               x_order_lines_tbl (l_counter).payment_term_id := 1001;
               get_item_id (p_line_tbl (i).line_index,
                            p_line_detail_tbl,
                            p_line_detail_attr_tbl,
                            x_order_lines_tbl (l_counter).inventory_item_id,
                            x_order_lines_tbl (l_counter).ordered_item
                           );
            END IF;

            EXIT WHEN i = p_line_tbl.LAST;
            i := p_line_tbl.NEXT (i);
            l_counter := l_counter + 1;
         END LOOP;
      END IF;

      DEBUG ('assign_lines Completed ');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('assign_lines' || SQLERRM);
   END assign_lines;

   -- Assign Promotions
   PROCEDURE assign_promotions (
      p_line_detail_tbl         IN       qp_preq_grp.line_detail_tbl_type,
      x_promotion_details_tbl   OUT      wbscus_order_promotions_obj
   )
   IS
      CURSOR cr_line_type (p_type_code VARCHAR2)
      IS
         SELECT SUBSTR (meaning, 1, 30)
           FROM qp_lookups
          WHERE 1 = 1
            AND lookup_type = 'LIST_LINE_TYPE_CODE'
            AND enabled_flag = 'Y'
            AND lookup_code = p_type_code
            AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active,
                                                    SYSDATE)
                                              )
                                    AND TRUNC (NVL (end_date_active, SYSDATE));

      CURSOR cr_promo_details (p_list_header_id NUMBER)
      IS
         SELECT SUBSTR (NAME, 1, 30), SUBSTR (description, 1, 30),
                SUBSTR (list_type_code, 1, 30)
           FROM qp_list_headers
          WHERE 1 = 1 AND list_header_id = p_list_header_id;

      CURSOR cr_promo_type (p_promo_type VARCHAR2)
      IS
         SELECT SUBSTR (meaning, 1, 30)
           FROM qp_lookups
          WHERE lookup_type = 'LIST_TYPE_CODE'
            AND lookup_code = p_promo_type
            AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active,
                                                    SYSDATE)
                                              )
                                    AND TRUNC (NVL (end_date_active, SYSDATE));

      l_pr_counter   NUMBER        := 1;
      l_promo_type   VARCHAR2 (30);
      i              NUMBER;
   BEGIN
      x_promotion_details_tbl := wbscus_order_promotions_obj ();
      i := p_line_detail_tbl.FIRST;
      DEBUG
         ('!!!!!!!!!!!!!!!!!!!!!!!!!!~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
         );

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG ('I is ' || i);
            DEBUG ('l_pr_counter    is ' || l_pr_counter);
            DEBUG ('Type Code ' || p_line_detail_tbl (i).list_line_type_code);

            ----- Assigning values to Out paramenter ---------
            IF p_line_detail_tbl (i).list_line_type_code <> 'PLL'
            THEN
               x_promotion_details_tbl.EXTEND;
               x_promotion_details_tbl (l_pr_counter) :=
                  wbscus_order_promotions_rec (NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL,
                                               NULL
                                              );

               OPEN cr_line_type (p_line_detail_tbl (i).list_line_type_code);

               FETCH cr_line_type
                INTO x_promotion_details_tbl (l_pr_counter).list_line_type_code;

               CLOSE cr_line_type;

               OPEN cr_promo_details (p_line_detail_tbl (i).list_header_id);

               FETCH cr_promo_details
                INTO x_promotion_details_tbl (l_pr_counter).promotion_name,
                     x_promotion_details_tbl (l_pr_counter).promotion_desc,
                     l_promo_type;

               OPEN cr_promo_type (l_promo_type);

               FETCH cr_promo_type
                INTO x_promotion_details_tbl (l_pr_counter).promotion_type;

               CLOSE cr_promo_type;

               CLOSE cr_promo_details;

               DEBUG ('assign_promotions Check-1');
               x_promotion_details_tbl (l_pr_counter).operand :=
                                           p_line_detail_tbl (i).operand_value;
               x_promotion_details_tbl (l_pr_counter).inventory_item_id :=
                                       p_line_detail_tbl (i).inventory_item_id;
               x_promotion_details_tbl (l_pr_counter).adjusted_amount :=
                                       p_line_detail_tbl (i).adjustment_amount;
               x_promotion_details_tbl (l_pr_counter).range_break_quantity :=
                                                                          NULL;
               x_promotion_details_tbl (l_pr_counter).accrual_conversion_rate :=
                                 p_line_detail_tbl (i).accrual_conversion_rate;
               x_promotion_details_tbl (l_pr_counter).pricing_group_sequence :=
                                  p_line_detail_tbl (i).pricing_group_sequence;
               DEBUG ('assign_promotions Check-2');
               x_promotion_details_tbl (l_pr_counter).benefit_qty :=
                                             p_line_detail_tbl (i).benefit_qty;
               x_promotion_details_tbl (l_pr_counter).operand_per_pqty := NULL;
               x_promotion_details_tbl (l_pr_counter).adjusted_amount_per_pqty :=
                                       p_line_detail_tbl (i).adjustment_amount;
               x_promotion_details_tbl (l_pr_counter).invoiced_amount := NULL;
               x_promotion_details_tbl (l_pr_counter).tax_rate_id := NULL;
               x_promotion_details_tbl (l_pr_counter).automatic_flag :=
                                          p_line_detail_tbl (i).automatic_flag;
               x_promotion_details_tbl (l_pr_counter).modified_from := NULL;
               x_promotion_details_tbl (l_pr_counter).modified_to := NULL;
               x_promotion_details_tbl (l_pr_counter).update_allowed := NULL;
               DEBUG ('assign_promotions Check-3');
               x_promotion_details_tbl (l_pr_counter).change_reason_code :=
                                      p_line_detail_tbl (i).change_reason_code;
               x_promotion_details_tbl (l_pr_counter).change_reason_text :=
                                      p_line_detail_tbl (i).change_reason_text;
               x_promotion_details_tbl (l_pr_counter).updated_flag :=
                                            p_line_detail_tbl (i).updated_flag;
               x_promotion_details_tbl (l_pr_counter).applied_flag :=
                                            p_line_detail_tbl (i).applied_flag;
               DEBUG ('assign_promotions Check-4');
               x_promotion_details_tbl (l_pr_counter).tax_code := NULL;
               x_promotion_details_tbl (l_pr_counter).charge_type_code :=
                                        p_line_detail_tbl (i).charge_type_code;
               x_promotion_details_tbl (l_pr_counter).charge_subtype_code :=
                                     p_line_detail_tbl (i).charge_subtype_code;
               x_promotion_details_tbl (l_pr_counter).accrual_flag :=
                                            p_line_detail_tbl (i).accrual_flag;
               x_promotion_details_tbl (l_pr_counter).list_line_no :=
                                            p_line_detail_tbl (i).list_line_no;
               x_promotion_details_tbl (l_pr_counter).benefit_uom_code :=
                                        p_line_detail_tbl (i).benefit_uom_code;
               DEBUG ('assign_promotions Check-5');
               x_promotion_details_tbl (l_pr_counter).print_on_invoice_flag :=
                                   p_line_detail_tbl (i).print_on_invoice_flag;
               x_promotion_details_tbl (l_pr_counter).modifier_level_code :=
                                     p_line_detail_tbl (i).modifier_level_code;
               x_promotion_details_tbl (l_pr_counter).include_on_returns_flag :=
                                 p_line_detail_tbl (i).include_on_returns_flag;
               x_promotion_details_tbl (l_pr_counter).arithmetic_operator :=
                                p_line_detail_tbl (i).operand_calculation_code;
               DEBUG ('assign_promotions Check-6');
               x_promotion_details_tbl (l_pr_counter).list_header_id :=
                                          p_line_detail_tbl (i).list_header_id;
               DEBUG (   'assign_promotions Check-6 '
                      || p_line_detail_tbl (i).list_header_id
                     );
               x_promotion_details_tbl (l_pr_counter).list_line_id :=
                                            p_line_detail_tbl (i).list_line_id;
               DEBUG (   'assign_promotions Check-6 '
                      || p_line_detail_tbl (i).list_line_id
                     );
               x_promotion_details_tbl (l_pr_counter).line_index :=
                                              p_line_detail_tbl (i).line_index;
               l_pr_counter := l_pr_counter + 1;
               DEBUG (   'assign_promotions Check-6 '
                      || p_line_detail_tbl (i).list_header_id
                     );
            END IF;

            EXIT WHEN i = p_line_detail_tbl.LAST;
            i := p_line_detail_tbl.NEXT (i);
         END LOOP;
      END IF;

      DEBUG ('assign_promotions Completed ');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('assign_promotions' || SQLERRM);
   END assign_promotions;

   PROCEDURE showpromotions (
      x_promotion_details_tbl   wbscus_order_promotions_obj
   )
   IS
      i   NUMBER;
   BEGIN
      i := x_promotion_details_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG (x_promotion_details_tbl (i).promotion_name);
            DEBUG (x_promotion_details_tbl (i).promotion_type);
            DEBUG (x_promotion_details_tbl (i).line_index);
            DEBUG (x_promotion_details_tbl (i).list_line_id);
            DEBUG (x_promotion_details_tbl (i).promotion_type);
            EXIT WHEN i = x_promotion_details_tbl.LAST;
            i := x_promotion_details_tbl.NEXT (i);
         END LOOP;
      END IF;
   END showpromotions;

   PROCEDURE call_engine (
      p_line_tbl                      qp_preq_grp.line_tbl_type,
      p_qual_tbl                      qp_preq_grp.qual_tbl_type,
      p_line_attr_tbl                 qp_preq_grp.line_attr_tbl_type,
      p_order_lines_tbl               wbscus_order_lines_obj,
      x_order_lines_tbl         OUT   wbscus_order_lines_obj,
      x_promotion_details_tbl   OUT   wbscus_order_promotions_obj
   )
   IS
      l_control_rec            qp_preq_grp.control_record_type;
      l_line_detail_tbl        qp_preq_grp.line_detail_tbl_type;
      l_line_detail_qual_tbl   qp_preq_grp.line_detail_qual_tbl_type;
      l_line_detail_attr_tbl   qp_preq_grp.line_detail_attr_tbl_type;
      l_related_lines_tbl      qp_preq_grp.related_lines_tbl_type;
      x_line_tbl               qp_preq_grp.line_tbl_type;
      x_line_qual              qp_preq_grp.qual_tbl_type;
      x_line_attr_tbl          qp_preq_grp.line_attr_tbl_type;
      x_line_detail_tbl        qp_preq_grp.line_detail_tbl_type;
      x_line_detail_qual_tbl   qp_preq_grp.line_detail_qual_tbl_type;
      x_line_detail_attr_tbl   qp_preq_grp.line_detail_attr_tbl_type;
      x_related_lines_tbl      qp_preq_grp.related_lines_tbl_type;
      x_return_status          VARCHAR2 (240);
      x_return_status_text     VARCHAR2 (240);
      qual_rec                 qp_preq_grp.qual_rec_type;
      line_attr_rec            qp_preq_grp.line_attr_rec_type;
      rltd_rec                 qp_preq_grp.related_lines_rec_type;
      l_debug_file             VARCHAR2 (200);
   BEGIN
      DEBUG ('Calling the Price Engine');
      x_promotion_details_tbl := wbscus_order_promotions_obj ();
      x_order_lines_tbl := wbscus_order_lines_obj ();
      l_control_rec.pricing_event := 'BATCH';
      l_control_rec.calculate_flag := 'Y';
      l_control_rec.simulation_flag := 'N';
      -- Actual Call to the Pricing Engine
      qp_preq_pub.price_request (p_line_tbl,
                                 p_qual_tbl,
                                 p_line_attr_tbl,
                                 l_line_detail_tbl,
                                 l_line_detail_qual_tbl,
                                 l_line_detail_attr_tbl,
                                 l_related_lines_tbl,
                                 l_control_rec,
                                 x_line_tbl,
                                 x_line_qual,
                                 x_line_attr_tbl,
                                 x_line_detail_tbl,
                                 x_line_detail_qual_tbl,
                                 x_line_detail_attr_tbl,
                                 x_related_lines_tbl,
                                 x_return_status,
                                 x_return_status_text
                                );
      -- Return Status Information ..
      DEBUG ('Return Status text ' || x_return_status_text);
      DEBUG ('Return Status  ' || x_return_status);
      DEBUG ('+---------Information Returned to Caller---------------------+ ');
      DEBUG ('-------------Request Line Information-------------------');
      DEBUG ('x_line_tbl Table Count is ' || x_line_tbl.COUNT);
      DEBUG ('x_line_qual Table Count is ' || x_line_qual.COUNT);
      DEBUG ('x_line_attr_tbl Table Count is ' || x_line_attr_tbl.COUNT);
      DEBUG ('x_line_detail_tbl Table Count is ' || x_line_detail_tbl.COUNT);
      DEBUG (   'x_line_detail_qual_tbl Table Count is '
             || x_line_detail_qual_tbl.COUNT
            );
      DEBUG (   'x_line_detail_attr_tbl Table Count is '
             || x_line_detail_attr_tbl.COUNT
            );
      DEBUG ('x_related_lines_tbl Table Count is '
             || x_related_lines_tbl.COUNT
            );
      display_line_table (x_line_tbl);
      display_line_table_qualifier (x_line_qual);
      display_line_table_attributes (x_line_attr_tbl);
      display_line_dtl_tabl (x_line_detail_tbl);
      display_line_dtl_qual (x_line_detail_qual_tbl);
      display_line_dtl_attributes (x_line_detail_attr_tbl);
      display_other_line_items (x_related_lines_tbl);
      assign_lines (x_line_tbl,
                    x_line_detail_tbl,
                    x_line_detail_attr_tbl,
                    p_order_lines_tbl,
                    x_order_lines_tbl
                   );
      assign_promotions (x_line_detail_tbl, x_promotion_details_tbl);
      DEBUG
         ('***************************************PROMOTION DETAILS****************************************'
         );
      showpromotions (x_promotion_details_tbl);
      DEBUG ('call_engine Completed ');
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in API ' || SQLERRM);
   END call_engine;

----------------------------------------------------------------------------------------------------
   PROCEDURE showdata (
      p_pricing_contexts_tbl     qp_attr_mapping_pub.contexts_result_tbl_type,
      p_qualifier_contexts_tbl   qp_attr_mapping_pub.contexts_result_tbl_type
   )
   IS
      i   PLS_INTEGER;
   BEGIN
      i := p_pricing_contexts_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG (   'Pricing Context :'
                   || p_pricing_contexts_tbl (i).context_name
                  );
            DEBUG (   'Pricing Attribute Name:'
                   || p_pricing_contexts_tbl (i).attribute_name
                  );
            DEBUG (   'Pricing Atribute Value :'
                   || p_pricing_contexts_tbl (i).attribute_value
                  );
            EXIT WHEN i = p_pricing_contexts_tbl.LAST;
            i := p_pricing_contexts_tbl.NEXT (i);
         END LOOP;
      END IF;

      i := p_qualifier_contexts_tbl.FIRST;

      IF i IS NOT NULL
      THEN
         LOOP
            DEBUG (   'Qualifier Context :'
                   || p_qualifier_contexts_tbl (i).context_name
                  );
            DEBUG (   'Qualifier Attribute Name:'
                   || p_qualifier_contexts_tbl (i).attribute_name
                  );
            DEBUG (   'Qualifier Atribute Value :'
                   || p_qualifier_contexts_tbl (i).attribute_value
                  );
            EXIT WHEN i = p_qualifier_contexts_tbl.LAST;
            i := p_qualifier_contexts_tbl.NEXT (i);
         END LOOP;
      END IF;
   END showdata;

----------------------------------------------------------------------------------------------------
   PROCEDURE copy_header_to_request (
      px_req_line_tbl          IN OUT NOCOPY   qp_preq_grp.line_tbl_type,
      p_calculate_price_flag                   VARCHAR2,
      p_line_index                             NUMBER
   )
   IS
      l_line_index   PLS_INTEGER := px_req_line_tbl.COUNT;
   BEGIN
      l_line_index := l_line_index + 1;
      px_req_line_tbl (l_line_index).request_type_code := g_request_type_code;
      px_req_line_tbl (l_line_index).line_index := p_line_index;
      px_req_line_tbl (l_line_index).line_type_code := 'ORDER';
      px_req_line_tbl (l_line_index).line_id := oe_order_pub.g_hdr.header_id;

      IF    oe_order_pub.g_hdr.pricing_date IS NULL
         OR oe_order_pub.g_hdr.pricing_date = fnd_api.g_miss_date
      THEN
         px_req_line_tbl (l_line_index).pricing_effective_date :=
                                                              TRUNC (SYSDATE);
      ELSE
         px_req_line_tbl (l_line_index).pricing_effective_date :=
                                              oe_order_pub.g_hdr.pricing_date;
      END IF;

      px_req_line_tbl (l_line_index).currency_code :=
                                    oe_order_pub.g_hdr.transactional_curr_code;
      px_req_line_tbl (l_line_index).price_flag := p_calculate_price_flag;
      px_req_line_tbl (l_line_index).active_date_first_type := 'ORD';
      px_req_line_tbl (l_line_index).active_date_first :=
                                               oe_order_pub.g_hdr.ordered_date;
   END copy_header_to_request;

   PROCEDURE price_request (
      p_order_header_rec        IN OUT   wbscus_order_header_obj,
      p_order_lines_tbl         IN       wbscus_order_lines_obj,
      x_order_lines_tbl         OUT      wbscus_order_lines_obj,
      x_promotion_details_tbl   OUT      wbscus_order_promotions_obj
   )
   IS
      l_line_attr_ctr            NUMBER                                  := 1;
      l_line_index               NUMBER                                  := 1;
      l_line_tbl                 qp_preq_grp.line_tbl_type;
      l_qual_tbl                 qp_preq_grp.qual_tbl_type;
      l_line_attr_tbl            qp_preq_grp.line_attr_tbl_type;
      l_pricing_contexts_tbl     qp_attr_mapping_pub.contexts_result_tbl_type;
      l_qualifier_contexts_tbl   qp_attr_mapping_pub.contexts_result_tbl_type;
   BEGIN
      g_debug := 'Y';
      initialize;
      --NDQ: Check whether it should Support MOAC. Should we read APPLICATION table and derive or safe to assume we can hardcode?
      mo_global.set_policy_context ('S', p_order_header_rec.org_id);
      -- Populate Header Record Structure
      x_order_lines_tbl := p_order_lines_tbl;
      DEBUG ('Populating the Header Record ' || p_order_lines_tbl.COUNT);
      set_header (p_order_header_rec);

      FOR i IN 1 .. p_order_lines_tbl.COUNT
      LOOP
         l_line_index := i;
         DEBUG (   'Populating the Line Record: '
                || i
                || ' with Line Number '
                || p_order_lines_tbl (i).line_number
                || ' Ordered Item '
                || p_order_lines_tbl (i).ordered_item
               );
         -- Populate Line Record Structure
         set_line (p_order_lines_tbl (i));
         DEBUG ('Building the Context');
         -- Build Contexts for Line
         qp_attr_mapping_pub.build_contexts
                       (p_request_type_code              => 'ONT',
                        p_pricing_type                   => 'L',
                        x_price_contexts_result_tbl      => l_pricing_contexts_tbl,
                        x_qual_contexts_result_tbl       => l_qualifier_contexts_tbl
                       );
         showdata (l_pricing_contexts_tbl, l_qualifier_contexts_tbl);
         DEBUG (   'Pricing Context Count is '
                || NVL (l_pricing_contexts_tbl.COUNT, 0)
               );
         DEBUG (   'Qualifier Context Count is '
                || NVL (l_qualifier_contexts_tbl.COUNT, 0)
               );
         DEBUG ('Copy Lines to Table');
         -- Copy to Line to Table
         copy_line_to_table (l_line_index, l_line_tbl);
         DEBUG (i || ': Line Table Count is ' || NVL (l_line_tbl.COUNT, 0));
         -- Copy to Table Reqeust
         DEBUG ('Copy Table to Request');
         copy_attributes_to_request (l_line_index,
                                     l_pricing_contexts_tbl,
                                     l_qualifier_contexts_tbl,
                                     l_line_attr_tbl,
                                     l_qual_tbl
                                    );
         DEBUG ('Line Attr Count is ' || NVL (l_line_attr_tbl.COUNT, 0));
         DEBUG ('Qualifier Count is ' || NVL (l_qual_tbl.COUNT, 0));
      END LOOP;

      -- Build Context for Header
      qp_attr_mapping_pub.build_contexts
                       (p_request_type_code              => 'ONT',
                        p_pricing_type                   => 'H',
                        x_price_contexts_result_tbl      => l_pricing_contexts_tbl,
                        x_qual_contexts_result_tbl       => l_qualifier_contexts_tbl
                       );
      showdata (l_pricing_contexts_tbl, l_qualifier_contexts_tbl);
      copy_header_to_request (l_line_tbl, 'Y', l_line_index + 1);
      DEBUG ('Line Table Count is ' || NVL (l_line_tbl.COUNT, 0));
      copy_attributes_to_request (l_line_index + 1,
                                  l_pricing_contexts_tbl,
                                  l_qualifier_contexts_tbl,
                                  l_line_attr_tbl,
                                  l_qual_tbl
                                 );
      -- Call Engine
      call_engine (l_line_tbl,
                   l_qual_tbl,
                   l_line_attr_tbl,
                   p_order_lines_tbl,
                   x_order_lines_tbl,
                   x_promotion_details_tbl
                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error : ' || SQLERRM);
   END price_request;

   FUNCTION get_order_type_id (p_order_type_id NUMBER)
      RETURN NUMBER
   IS
      CURSOR cr_order_types
      IS
         SELECT legacy_record_id
           FROM xxbol.wbs_setup_order_types
          WHERE legacy_system_id = g_legacy_system_id
            AND order_type_id = p_order_type_id;

      l_order_type_id   NUMBER;
   BEGIN
      l_order_type_id := NULL;

      OPEN cr_order_types;

      FETCH cr_order_types
       INTO l_order_type_id;

      CLOSE cr_order_types;

      RETURN l_order_type_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_order_type_id;

   FUNCTION get_customer_id (p_customer_id NUMBER)
      RETURN NUMBER
   IS
      CURSOR cr_customers
      IS
         SELECT legacy_record_id
           FROM xxbol.wbs_customers wc
          WHERE wc.customer_id = p_customer_id;

      l_customer_id   NUMBER;
   BEGIN
      l_customer_id := NULL;

      OPEN cr_customers;

      FETCH cr_customers
       INTO l_customer_id;

      CLOSE cr_customers;

      RETURN l_customer_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_customer_id;

   FUNCTION get_site_use_id (p_address_id NUMBER)
      RETURN NUMBER
   IS
      CURSOR cr_addresses
      IS
         SELECT legacy_record_id
           FROM xxbol.wbs_address
          WHERE address_id = p_address_id
            AND legacy_system_id = g_legacy_system_id;

      l_site_use_id   NUMBER;
   BEGIN
      l_site_use_id := NULL;

      OPEN cr_addresses;

      FETCH cr_addresses
       INTO l_site_use_id;

      CLOSE cr_addresses;

      RETURN l_site_use_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_site_use_id;

   FUNCTION get_price_list_id (p_list_line_id NUMBER)
      RETURN NUMBER
   IS
      CURSOR cr_price_list_lines
      IS
         SELECT list_header_id
           FROM qp_list_lines
          WHERE list_line_id = p_list_line_id;

      l_price_list_id   NUMBER;
   BEGIN
      l_price_list_id := NULL;

      OPEN cr_price_list_lines;

      FETCH cr_price_list_lines
       INTO l_price_list_id;

      CLOSE cr_price_list_lines;

      RETURN l_price_list_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END;

   FUNCTION get_order_source_id (p_order_source_name VARCHAR2)
      RETURN NUMBER
   IS
      CURSOR cr_order_sources
      IS
         SELECT order_source_id
           FROM oe_order_sources
          WHERE NAME = p_order_source_name;
   BEGIN
      g_order_source_id := NULL;

      OPEN cr_order_sources;

      FETCH cr_order_sources
       INTO g_order_source_id;

      CLOSE cr_order_sources;

      RETURN g_order_source_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_order_source_id;

   PROCEDURE set_apex_ebs_header (
      p_header_rec           IN OUT   wbscus_order_header_obj,
      p_customer_id                   NUMBER,
      p_order_type_id                 NUMBER,
      p_ship_to_address_id            NUMBER,
      p_bill_to_address_id            NUMBER,
      p_price_list_line_id            NUMBER
   )
   IS
      CURSOR cr_defaults
      IS
         SELECT *
           FROM xxbol.wbs_setup_bol_app
          WHERE enable_configuration = 'Y' AND ROWNUM = 1;
   BEGIN
      DEBUG ('Procedure set_apex_ebs_header Started');
      p_header_rec :=
         wbscus_order_header_obj (NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL,
                                  NULL
                                 );

      FOR cr_rec IN cr_defaults
      LOOP
         p_header_rec.sold_to_customer_id := get_customer_id (p_customer_id);
         p_header_rec.order_type_id := get_order_type_id (p_order_type_id);
         p_header_rec.ship_to_org_id :=
                                       get_site_use_id (p_ship_to_address_id);
         p_header_rec.invoice_to_org_id :=
                                       get_site_use_id (p_bill_to_address_id);
         p_header_rec.price_list_id :=
                                     get_price_list_id (p_price_list_line_id);
         p_header_rec.order_source_id :=
                                get_order_source_id (cr_rec.bol_order_source);
         p_header_rec.order_date := SYSDATE;
         p_header_rec.org_id := cr_rec.pe_org_id;
         p_header_rec.ship_from_org_id := cr_rec.pe_organization_id;
         -- Set the Apps Context
         DEBUG ('Setting the Apps Context');
         fnd_global.apps_initialize (cr_rec.pe_user_id,
                                     cr_rec.pe_responsibility_id,
                                     cr_rec.pe_resp_application_id
                                    );
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in set_apex_ebs_header, Error is ' || SQLERRM);
   END set_apex_ebs_header;

   PROCEDURE set_apex_ebs_line (
      p_line_rec            IN OUT   wbscus_order_lines_obj,
      p_position                     NUMBER,
      p_line_number                  NUMBER,
      p_inventory_item_id            NUMBER,
      p_quantity                     NUMBER,
      p_primary_uom_code             VARCHAR2
   )
   IS
   BEGIN
      p_line_rec.EXTEND;
      p_line_rec (p_position) :=
         wbscus_order_line_rec (NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL,
                                NULL
                               );
      -- Line Record
      p_line_rec (p_position).line_number := p_line_number;
      p_line_rec (p_position).inventory_item_id := p_inventory_item_id;
      p_line_rec (p_position).ordered_quantity := p_quantity;
      p_line_rec (p_position).request_date := SYSDATE;
      p_line_rec (p_position).promise_date := SYSDATE;
      p_line_rec (p_position).primary_uom_code := p_primary_uom_code;
      DEBUG ('Line Setup is Completed @ Position ' || p_position);
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in set_apex_ebs_line, Error is ' || SQLERRM);
   END set_apex_ebs_line;

   PROCEDURE set_ebs_apex_prices (
      p_lines_tbl        wbscus_order_lines_obj,
      p_promotions_tbl   wbscus_order_promotions_obj,
      p_cart_id          NUMBER,
      p_mode             VARCHAR2
   )
   IS
   BEGIN
      DEBUG (' Procedure set_ebs_apex_prices Started');

      IF p_lines_tbl IS NOT NULL
      THEN
         DEBUG (   ' Lines Table is not null and has '
                || p_lines_tbl.COUNT
                || ' rows'
               );

         FOR i IN 1 .. p_lines_tbl.COUNT
         LOOP
            UPDATE xxbol.wbs_cart_lines
               SET order_price = p_lines_tbl (i).unit_selling_price
             WHERE cart_id = p_cart_id
               AND cart_line_no = p_lines_tbl (i).line_number;

            IF SQL%ROWCOUNT = 0
            THEN
               NULL;
               DEBUG (' Free Item Added to the Results');
            -- Insert Free Item;
            ELSE
               DEBUG (   SQL%ROWCOUNT
                      || ' Record(s) Updated for Line '
                      || p_lines_tbl (i).line_number
                      || ' with Price '
                      || p_lines_tbl (i).unit_selling_price
                     );
            END IF;
         END LOOP;
      END IF;

      IF p_promotions_tbl IS NOT NULL
      THEN
         DELETE FROM xxbol.wbs_cart_modifiers
               WHERE cart_id = p_cart_id;

         DEBUG (   'Promotions Table is not null and has '
                || p_promotions_tbl.COUNT
                || ' rows'
               );

         FOR i IN 1 .. p_promotions_tbl.COUNT
         LOOP
            INSERT INTO xxbol.wbs_cart_modifiers
                        (cart_modifier_id, cart_id, cart_line_no,
                         list_header_id,
                         list_line_id,
                         operand,
                         adjusted_amout,
                         range_break_quantity,
                         pricing_group_sequence,
                         benefit_qty,
                         operand_per_pqty,
                         adjusted_amount_per_pqty,
                         arithmetic_operator,
                         promotion_name,
                         promotion_description,
                         promotion_type,
                         created_by, creation_date, last_update_date,
                         last_updated_by
                        )
                 VALUES (NULL, p_cart_id, p_promotions_tbl (i).line_index,
                         p_promotions_tbl (i).list_header_id,
                         p_promotions_tbl (i).list_line_id,
                         p_promotions_tbl (i).operand,
                         p_promotions_tbl (i).adjusted_amount,
                         p_promotions_tbl (i).range_break_quantity,
                         p_promotions_tbl (i).pricing_group_sequence,
                         p_promotions_tbl (i).benefit_qty,
                         p_promotions_tbl (i).operand_per_pqty,
                         p_promotions_tbl (i).adjusted_amount_per_pqty,
                         p_promotions_tbl (i).arithmetic_operator,
                         p_promotions_tbl (i).promotion_name,
                         p_promotions_tbl (i).promotion_desc,
                         p_promotions_tbl (i).promotion_type,
                         fnd_global.user_id, SYSDATE, SYSDATE,
                         fnd_global.user_id
                        );
         END LOOP;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DEBUG ('Error in set_ebs_apex_prices, Error is ' || SQLERRM);
   END set_ebs_apex_prices;

   PROCEDURE price_order (
      p_cart_id                NUMBER,
      p_mode                   VARCHAR2,
      p_return_status    OUT   VARCHAR2,
      p_return_message   OUT   VARCHAR2
   )
   IS
      CURSOR cr_order_lines
      IS
         SELECT wi.legacy_record_id inventory_item_id, wcl.order_qty,
                wcl.cart_line_no, wcl.cart_line_id, wcl.order_price,
                wch.order_type_id, wch.order_source, wch.customer_id,
                wch.bill_to_address_id, wch.ship_to_address_id,
                wsu.legacy_record_id primary_uom_code,
                wcl.price_list_line_id
           FROM xxbol.wbs_cart_headers wch,
                xxbol.wbs_cart_lines wcl,
                xxbol.wbs_items wi,
                xxbol.wbs_setup_uom wsu
          WHERE wch.cart_id = p_cart_id
            AND wcl.cart_id = wch.cart_id
            AND wi.item_id = wcl.item_id
            AND wi.legacy_system_id = g_legacy_system_id
            AND wsu.setup_uom_id = wcl.setup_uom_id
            AND wsu.legacy_system_id = g_legacy_system_id;

      l_header_rec       wbscus_order_header_obj;
      l_line_rec         wbscus_order_lines_obj   := wbscus_order_lines_obj
                                                                           ();
      l_lines_tbl        wbscus_order_lines_obj   := wbscus_order_lines_obj
                                                                           ();
      x_lines_tbl        wbscus_order_lines_obj   := wbscus_order_lines_obj
                                                                           ();
      x_promotions_tbl   wbscus_order_promotions_obj
                                             := wbscus_order_promotions_obj
                                                                           ();
      i                  NUMBER                      := 0;
   BEGIN
      p_return_status := 'S';
      p_return_message := NULL;
      DEBUG ('Price Order Started');
      i := 1;

      FOR cr_order_rec IN cr_order_lines
      LOOP
         set_apex_ebs_line (l_line_rec,
                            i,
                            cr_order_rec.cart_line_no,
                            cr_order_rec.inventory_item_id,
                            cr_order_rec.order_qty,
                            cr_order_rec.primary_uom_code
                           );

         IF i = 1
         THEN
            ---- Set Header Record
            set_apex_ebs_header (l_header_rec,
                                 cr_order_rec.customer_id,
                                 cr_order_rec.order_type_id,
                                 cr_order_rec.ship_to_address_id,
                                 cr_order_rec.bill_to_address_id,
                                 cr_order_rec.price_list_line_id
                                );
         END IF;

         i := i + 1;
      END LOOP;

      -- Ensure Cursor has entered into the above loop atleast one time. Order has atleast one item.
      IF i > 1
      THEN
         DEBUG (' Order has ' || (i - 1) || ' lines, Calling Pricing Engine ');
         wbscus_qp_pricing_engine_pkg.price_request
                                 (p_order_header_rec           => l_header_rec,
                                  p_order_lines_tbl            => l_line_rec,
                                  x_order_lines_tbl            => x_lines_tbl,
                                  x_promotion_details_tbl      => x_promotions_tbl
                                 );
         DEBUG (' Pricing Engine Call completed ');
         set_ebs_apex_prices (x_lines_tbl, x_promotions_tbl, p_cart_id,
                              p_mode);

         IF (p_mode != 'TEST')
         THEN
            COMMIT;
         END IF;
      END IF;
   END price_order;
END wbscus_qp_pricing_engine_pkg;
/
